﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Community.CsharpSqlite.SQLiteClient;
using NReader.Model;
using NReader.Model.RAS;
using Windows.Storage;

namespace Local.Service
{
    public class LocalSqliteService
    {
        #region Initialization

        public static LocalSqliteService Service;
        public static SqliteConnection Connection;

        public static LocalSqliteService Instance()
        {
            if (Service == null)
            {
                Service = new LocalSqliteService();
                if (Connection == null)
                {
                    Connection = new SqliteConnection();

                    var dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"NReader.db");
                    var cs = string.Format("Version=3,uri=file:{0}", dbFilename);
                    if (!Utils.FileExists(dbFilename))
                    {
                        Connection.ConnectionString = cs;
                        Connection.Open();
                        CreateTables();
                    }
                    else
                    {
                        Connection.ConnectionString = cs;
                        Connection.Open();
                    }
                }
            }
            return Service;
        }

        private static void CreateTables()
        {
            //create table
            IDbCommand cmd = Connection.CreateCommand();
            var sb = new StringBuilder();

            sb.Append("CREATE TABLE [metro_article] (");
            sb.Append("  [articleid] INT(11) NOT NULL, ");
            sb.Append("  [lastupdate] INT(11), ");
            sb.Append("  [articlename] VARCHAR(30), ");
            sb.Append("  [author] VARCHAR(30), ");
            sb.Append("  [keywords] VARCHAR(50), ");
            sb.Append("  [sortid] INT, ");
            sb.Append("  [intro] VARCHAR(300), ");
            sb.Append("  [lastchapterid] INT(11), ");
            sb.Append("  [lastchapter] VARCHAR(100), ");
            sb.Append("  [chapters] INT, ");
            sb.Append("  [display] INT, ");
            sb.Append("  [sortname] VARCHAR(30), ");
            sb.Append("  [groupname] VARCHAR(30), ");
            sb.Append("  [writeprogress] VARCHAR(30), ");
            sb.Append("  [updatetime] INT, ");
            sb.Append("  [coverlink] VARCHAR(100), ");
            sb.Append("  [historyprogress] FLOAT DEFAULT (0), ");
            sb.Append("  [articletype] NVARCHAR(10), ");
            sb.Append("  [online] INT DEFAULT (1), ");
            sb.Append("  [historychapterid] INT DEFAULT (0), ");
            sb.Append("  [historychapter] NVARCHAR(100), ");
            sb.Append("  CONSTRAINT [sqlite_autoindex_metro_article_1] PRIMARY KEY ([articleid]));");

            sb.Append("CREATE TABLE [metro_bookcase] (");
            sb.Append("  [id] INT(11) NOT NULL, ");
            sb.Append("  [casename] NVARCHAR(30), ");
            sb.Append("  [remark] NVARCHAR(50), ");
            sb.Append("  [createuser] NVARCHAR(30), ");
            sb.Append("  [createtime] INT(18), ");
            sb.Append("  [updateuser] NVARCHAR(30), ");
            sb.Append("  [updatetime] INT(18));");
            sb.Append("");
            sb.Append("CREATE INDEX [index_bookcase] ON [metro_bookcase] ([id]);");

            sb.Append("CREATE TABLE [metro_bookstore] (");
            sb.Append("  [articleid] INT(11) NOT NULL, ");
            sb.Append("  [updatetime] INT, ");
            sb.Append("  [bookcaseid] INT NOT NULL DEFAULT (1), ");
            sb.Append("  CONSTRAINT [sqlite_autoindex_metro_bookstore_1] PRIMARY KEY ([articleid]));");

            sb.Append("CREATE TABLE [version] (");
            sb.Append("  [id] INT(11) NOT NULL, ");
            sb.Append("  [version] NVARCHAR(50) NOT NULL, ");
            sb.Append("  [isactive] INT NOT NULL DEFAULT (1), ");
            sb.Append("  [updatetime] INT(18));");

            //生成必要数据
            sb.Append("insert into metro_bookcase (id,casename,remark) values(1,'默认书架','System');");
            sb.Append("insert into metro_bookcase (id,casename,remark) values(2,'我的书架1','System');");
            sb.Append("insert into metro_bookcase (id,casename,remark) values(3,'我的书架2','System');");

            sb.Append("insert into version(id,version,isactive) values(1,'2.0.1',1);");

            //sql.Append(" CREATE TABLE [metro_article] (");
            //sql.Append(" [articleid] INT(11) NOT NULL, ");
            //sql.Append(" [lastupdate] INT(11), ");
            //sql.Append(" [articlename] VARCHAR(30), ");
            //sql.Append(" [author] VARCHAR(30), ");
            //sql.Append(" [keywords] VARCHAR(50), ");
            //sql.Append(" [sortid] INT, ");
            //sql.Append(" [intro] VARCHAR(300), ");
            //sql.Append(" [lastchapterid] INT(11), ");
            //sql.Append(" [lastchapter] VARCHAR(100), ");
            //sql.Append(" [chapters] INT, ");
            //sql.Append(" [display] INT, ");
            //sql.Append(" [sortname] VARCHAR(30), ");
            //sql.Append(" [groupname] VARCHAR(30), ");
            //sql.Append(" [writeprogress] VARCHAR(30), ");
            //sql.Append(" [updatetime] INT, ");
            //sql.Append(" [coverlink] VARCHAR(100), ");
            //sql.Append(" [historyprogress] FLOAT,");
            //sql.Append(" [articletype] NVARCHAR(10),");
            //sql.Append(" [online] INT DEFAULT (1),");
            //sql.Append(" CONSTRAINT [sqlite_autoindex_metro_article_1] PRIMARY KEY ([articleid]));");

            //sql.Append("   CREATE TABLE [metro_bookstore] (");
            //sql.Append("   [articleid] INT(11) NOT NULL, [updatetime] INT, ");
            //sql.Append("   [bookcaseid] INT NOT NULL DEFAULT (1), ");
            //sql.Append("   CONSTRAINT [sqlite_autoindex_metro_bookstore_1] PRIMARY KEY ([articleid]));");

            //sql.Append(" CREATE TABLE [metro_bookcase] (");
            //sql.Append("   [id] INT(11) NOT NULL, ");
            //sql.Append("   [casename] NVARCHAR(30), ");
            //sql.Append("   [remark] NVARCHAR(50), ");
            //sql.Append("   [createuser] NVARCHAR(30),");
            //sql.Append("   [createtime] INT(18), ");
            //sql.Append("   [updateuser] NVARCHAR(30), ");
            //sql.Append("   [updatetime] INT(18));");
            //sql.Append(" CREATE INDEX [index_bookcase] ON [metro_bookcase] ([id]);");

            cmd.CommandText = sb.ToString();
            cmd.ExecuteNonQuery();
        }

        #endregion

        #region Bookstore

        public async Task<bool> AddBookStore(int articleid,int bookcaseid)
        {
            try
            {
                IDbCommand cmd = Connection.CreateCommand();
                cmd.CommandText =
                    string.Format(
                        " delete from metro_bookstore where articleid={0}; insert into metro_bookstore(articleid,updatetime,bookcaseid) values({0},{1},{2});",
                        articleid.ToString(), DateTime.Now.ToString("yyyyMMddHHmmss"), bookcaseid);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
        }

        /// <summary>
        /// 根据书架ID号获取书籍列表
        /// </summary>
        /// <param name="bookcaseid"></param>
        /// <returns></returns>
        public async Task<IList<ArticleInfo>> GetBookStore(int bookcaseid)
        {
            var list = new List<ArticleInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = string.Format("select a.articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                                            "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress,historyprogress,articletype,online,historychapterid " +
                                            "from metro_bookstore as a left join metro_article as b on a.articleid=b.articleid where a.bookcaseid={0} order by b.updatetime desc",
                                            bookcaseid);
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new ArticleInfo
                                {
                                    articleid = reader.GetInt32(0),
                                    lastupdate = reader.GetString(1),
                                    articlename = reader.GetString(2),
                                    keywords = reader.GetString(3),
                                    author = reader.GetString(4),
                                    sortid = reader.GetInt32(5),
                                    intro = reader.GetString(6),
                                    lastchapterid = reader.GetInt32(7),
                                    lastchapter = reader.GetString(8),
                                    chapters = reader.GetInt32(9),
                                    display = reader.GetInt32(10),
                                    sortname = reader.GetString(11),
                                    coverlink = reader.GetString(12),
                                    writeprogress = reader.GetString(13),
                                    historyprogress = reader.GetDecimal(14),
                                    articletype = reader.GetString(15),
                                    online = reader.GetInt32(16),
                                    historychapterid = reader.GetInt32(17),
                                    GroupName = "我的书架"
                                };
                list.Add(model);
            }
            return list;
        }

        public async Task<int> GetBookStoreCount(int bookcaseid)
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = string.Format(" select count(articleid)" +
                                            " from metro_bookstore where bookcaseid={0}", bookcaseid);
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                return reader.GetInt32(0);
            }
            return 0;
        }

        public async Task<bool> DeleteBookStore(string articleids)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_bookstore where articleid in " + articleids + ";");
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() >= 1;
        }

        /// <summary>
        /// 单笔删除书架内容
        /// </summary>
        /// <param name="articleid"></param>
        /// <returns></returns>
        public async Task<bool> DeleteBookStore(int articleid)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_bookstore where articleid = " + articleid + ";");
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() >= 1;
        }

        public async Task<BookcaseInfo> GetBookcase(int bookcaseid)
        {
            IList<BookcaseInfo> list = new List<BookcaseInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = string.Format(
                "select a.[id],a.[casename],a.[remark],ifnull(count(b.[articleid]),0) as total from metro_bookcase a " +
                "left join metro_bookstore b on a.[id]=b.bookcaseid where id={0} group by a.[id],a.[casename],a.[remark]",
                bookcaseid);
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new BookcaseInfo
                {
                    id = reader.GetInt32(0),
                    casename = reader.GetString(1),
                    remark = reader.GetString(2),
                    total = reader.GetInt32(3)
                };
                return model;
            }
            return null;
        }

        /// <summary>
        /// 获取本地所有书架，汇总书籍数量
        /// </summary>
        /// <returns></returns>
        public async Task<IList<BookcaseInfo>> GetBookcases()
        {
            IList<BookcaseInfo> list = new List<BookcaseInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText =
                "select a.[id],a.[casename],a.[remark],ifnull(count(b.[articleid]),0) as total from metro_bookcase a " +
                "left join metro_bookstore b on a.[id]=b.bookcaseid group by a.[id],a.[casename],a.[remark]";
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new BookcaseInfo
                                {
                                    id = reader.GetInt32(0),
                                    casename = reader.GetString(1),
                                    remark = reader.GetString(2),
                                    total = reader.GetInt32(3)
                                };
                list.Add(model);
            }
            return list;
        }

        #endregion

        #region Book

        public async Task<ArticleInfo> GetArticle(int articleid)
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = "select a.articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                              "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress " +
                              "from metro_bookstore as a left join metro_article as b on a.articleid=b.articleid " +
                              "where a.articleid=@articleid";
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articleid", Value = articleid});
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var model = new ArticleInfo
                                {
                                    articleid = articleid,
                                    lastupdate = reader.GetString(1),
                                    articlename = reader.GetString(2),
                                    keywords = reader.GetString(3),
                                    author = reader.GetString(4),
                                    sortid = reader.GetInt32(5),
                                    intro = reader.GetString(6),
                                    lastchapterid = reader.GetInt32(7),
                                    lastchapter = reader.GetString(8),
                                    chapters = reader.GetInt32(9),
                                    display = reader.GetInt32(10),
                                    sortname = reader.GetString(11),
                                    coverlink = reader.GetString(12),
                                    writeprogress = reader.GetString(13)
                                };
                return model;
            }
            return null;
        }

        public async Task<bool> AddBook(ArticleInfo article)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_article where articleid=@articleid;");
            strSql.Append(" insert into metro_article(");
            strSql.Append(" articleid,lastupdate,articlename,keywords,author,");
            strSql.Append(" sortid,intro,lastchapterid,lastchapter,chapters,");
            strSql.Append(" display,coverlink,writeprogress,sortname,updatetime,historyprogress,articletype,online,historychapterid)");
            strSql.Append(" values(@articleid,@lastupdate,@articlename,@keywords,@author,");
            strSql.Append(" @sortid,@intro,@lastchapterid,@lastchapter,@chapters,");
            strSql.Append(" @display,@coverlink,@writeprogress,@sortname,@updatetime,@historyprogress,@articletype,@online,@historychapterid)");
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articleid", Value = article.articleid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastupdate", Value = article.lastupdate});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articlename", Value = article.articlename});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@keywords", Value = article.keywords});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@author", Value = article.author});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@sortid", Value = article.sortid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@intro", Value = article.intro});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastchapterid", Value = article.lastchapterid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastchapter", Value = article.lastchapter});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@chapters", Value = article.chapters});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@display", Value = article.display});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@coverlink", Value = article.coverlink});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@writeprogress", Value = article.writeprogress});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@sortname", Value = article.sortname});
            cmd.Parameters.Add(new SqliteParameter
                                   {ParameterName = "@updatetime", Value = DateTime.Now.ToString("yyyyMMddHHmmss")});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@historyprogress", Value = article.historyprogress});
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@articletype", Value = article.articletype });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@online", Value = article.online });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@historychapterid", Value = article.historychapterid });
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() == 1;
        }

        public async Task<bool> DeleteBook(ArticleInfo article)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_article where articleid=@articleid;");
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articleid", Value = article.articleid});
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() == 1;
        }

        /// <summary>
        /// 获取最大ID号，用以本地导入的ID
        /// </summary>
        /// <param name="minid">从100000000开始为本地导入的ID</param>
        /// <returns></returns>
        public async Task<int> GetMaxID(int minid)
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = " select ifnull(max(articleid),@minid)+1 from metro_article" +
                              " where articleid>@minid";
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@minid", Value = minid });
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                return reader.GetInt32(0);
            }
            return minid+1;
        }

        /// <summary>
        /// 根据名称判断文件是否存在
        /// </summary>
        /// <param name="articlename"></param>
        /// <returns>true存在</returns>
        public async Task<bool> ExistsArticle(string articlename)
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = " select count(*) from metro_bookstore as a left join metro_article as b on a.[articleid]=b.articleid" +
                              " where articlename=@articlename";
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@articlename", Value = articlename });
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                return !reader.GetInt32(0).Equals(0);
            }
            return false;
        }

        public async Task<bool> UpdateBook(ArticleInfo article)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();

            strSql.Append(" update metro_article set historyprogress=@historyprogress," +
                          "historychapterid=@historychapterid,historychapter=@historychapter,updatetime=@updatetime");
            strSql.Append(" where articleid=@articleid");
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@historyprogress", Value = article.historyprogress });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@historychapterid", Value = article.historychapterid });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@historychapter", Value = article.historychapter });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@articleid", Value = article.articleid });
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@updatetime", Value = article.updatetime });
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() == 1;
        }

        #endregion

        #region Index

        public async Task<IList<ArticleInfo>> GetHistoryList(int count)
        {
            //阅读进度>0作为查询条件
            var list = new List<ArticleInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = "select articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                              "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress,historyprogress,articletype,online,updatetime,historychapterid " +
                              "from metro_article where historyprogress>0 order by updatetime desc limit 0,@count";
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@count", Value = count});
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new ArticleInfo
                                {
                                    articleid = reader.GetInt32(0),
                                    lastupdate = reader.GetString(1),
                                    articlename = reader.GetString(2),
                                    keywords = reader.GetString(3),
                                    author = reader.GetString(4),
                                    sortid = reader.GetInt32(5),
                                    intro = reader.GetString(6),
                                    lastchapterid = reader.GetInt32(7),
                                    lastchapter = reader.GetString(8),
                                    chapters = reader.GetInt32(9),
                                    display = reader.GetInt32(10),
                                    sortname = reader.GetString(11),
                                    coverlink = reader.GetString(12),
                                    writeprogress = reader.GetString(13),
                                    historyprogress = reader.GetDecimal(14),
                                    articletype = reader.GetString(15),
                                    online = reader.GetInt32(16),
                                    updatetime = reader.GetString(17),
                                    historychapterid=reader.GetInt32(18),
                                    GroupName = "最近阅读"
                                };
                list.Add(model);
            }
            return list;
        }



        #endregion
    }
}
